﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;

public partial class BCDTNVKD : System.Web.UI.Page
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    NpoiExport cl = new NpoiExport();
    string frmName = "frmBCDTNVKD";
    static int f_SuDung = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static int PageNumber = 10;
    static int CurrentPage = 1;
    static double kltx = 0, klban = 0, ttban = 0, tongchenh = 0, chenhnvkd = 0, ttchenh = 0, ttchenhnvkd = 0, htrocuoc = 0, tiencabom = 0;
    static DataTable dt,dtPrint;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])) || string.IsNullOrEmpty(Convert.ToString(Session["IDUser"])))
            Response.Redirect("Default.aspx");
        if (!IsPostBack)
        {
            string idnguoidung = Session["IDUser"].ToString();
            string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

            System.Data.DataTable dt2 = ex.GetData_Text(sql);
            if (dt2.Rows.Count > 0)
            {
                f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
            }


            CurrentPage = 1;
            Title = "Báo cáo doanh thu theo biển số xe | " + Header;
            for (int i = 1; i <= 12; i++)
            {
                dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
            }
            dlThang.Items.Insert(0, new ListItem("Theo thời gian", ""));
            dlThang.SelectedIndex = 0;

            int year1 = DateTime.Now.Year - 3;
            int year2 = DateTime.Now.Year;
            for (int i = year2; i >= year1; i--)
            {
                dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
            }
            dlNam.SelectedValue = year2.ToString();

            txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            dlNam.Visible = false;

            LoadNVKD(dlNhanVien);
            LoadHinhThuc(dlHinhThuc);
        }
    }
    void LoadNVKD(DropDownList dlNV)
    {
        dlNV.Items.Clear();
        dlNV.Items.Insert(0, new ListItem("--Chọn nhân viên kinh doanh--", ""));
        dlNV.SelectedIndex = 0;
    }
    void LoadHinhThuc(DropDownList dlHinhThuc)
    {
        dlHinhThuc.Items.Clear();
        dlHinhThuc.Items.Insert(0, new ListItem("--Chọn hình thức HĐ--", "0"));
        dlHinhThuc.SelectedIndex = 0;
    }
    void LoadNVKDByTime(DropDownList dlNV)
    {
        dlNV.Items.Clear();
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        dlNV.DataSource = ex.GetData("sp_BCDT_NVKD_LoadNVByTime",p);
        dlNV.DataBind();
        dlNV.Items.Insert(0, new ListItem("--Chọn nhân viên--", ""));
        dlNV.SelectedIndex = 0;
    }
    protected void dlLoaiBaoCao_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiBaoCao.SelectedIndex.Equals(0))
            Response.Redirect("BCDTTongHop.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(1))
            Response.Redirect("BCDTMacHopDong.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(2))
            Response.Redirect("BCDTBienSoXe.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(3))
            Response.Redirect("BCDTNVKD.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(4))
            Response.Redirect("BCDTXeBom.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(5))
            Response.Redirect("BCDTGiaVatLieu.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(6))
            Response.Redirect("BCDTKhachHang.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(7))
            Response.Redirect("BCDTVatLieuTieuHao.aspx");
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            txtTuNgay.Visible = true;
            txtDenNgay.Visible = true;
            dlNam.Visible = false;
        }
        else
        {
            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;
            dlNam.Visible = true;
        }

        getDate();

        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        LoadNVKDByTime(dlNhanVien);

    }
    public void getDate()
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
        else
        {
            txtTuNgay.Text = "1/" + dlThang.SelectedValue + "/" + dlNam.SelectedValue;
            txtDenNgay.Text = GetLastDayOfMonth(int.Parse(dlThang.SelectedValue)).ToString("dd/MM/yyyy");
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(dlNam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }
    protected void btnXem_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;
        Search(1);
        btnFirst.Enabled = false;
        btnNext.Enabled = true;
        btnPre.Enabled = false;

        
    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        int index = 1;
        if (dlNhanVien.SelectedIndex.Equals(0) && dlHinhThuc.SelectedIndex.Equals(0))
            index = 1;
        else if (dlNhanVien.SelectedIndex > 0 && dlHinhThuc.SelectedIndex.Equals(0))
            index = 2;
        else if (dlNhanVien.SelectedIndex > 0 && dlHinhThuc.SelectedIndex > 0)
            index = 3;

        getDate();
        p = new SqlParameter[5];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@LoaiHD", int.Parse(dlHinhThuc.SelectedValue));
        p[3].SqlDbType = SqlDbType.Int;
        p[4] = new SqlParameter("@Index", index);
        p[4].SqlDbType = SqlDbType.Int;

        try
        {
            dt = ex.GetData("sp_BCDT_NVKD_Print", p);
            PrintNPOI(dt);
        }
        catch (Exception ax)
        {
            gstGetMess(ax.Message, "");
        }
    }
    void Search(int page)
    {
        int index = 1;
        if (dlNhanVien.SelectedIndex.Equals(0) && dlHinhThuc.SelectedIndex.Equals(0))
            index = 1;
        else if (dlNhanVien.SelectedIndex>0 && dlHinhThuc.SelectedIndex.Equals(0))
            index = 2;
        else if (dlNhanVien.SelectedIndex>0 && dlHinhThuc.SelectedIndex>0)
            index = 3;

        getDate();
        p = new SqlParameter[7];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@LoaiHD", int.Parse(dlHinhThuc.SelectedValue));
        p[3].SqlDbType = SqlDbType.Int;
        p[4] = new SqlParameter("@Index", index);
        p[4].SqlDbType = SqlDbType.Int;
        p[5] = new SqlParameter("@PageNumber", page);
        p[5].SqlDbType = SqlDbType.Int;
        p[6] = new SqlParameter("@RowspPage", PageNumber);
        p[6].SqlDbType = SqlDbType.Int;
        
        dt=ex.GetData("sp_BCDT_NVKD", p);
        
        GV.DataSource = dt;
        GV.DataBind();

        if (CurrentPage.Equals(1))
        {
            p = new SqlParameter[5];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@LoaiHD", int.Parse(dlHinhThuc.SelectedValue));
            p[3].SqlDbType = SqlDbType.Int;
            p[4] = new SqlParameter("@Index", index);
            p[4].SqlDbType = SqlDbType.Int;

            dt = ex.GetData("sp_BCDT_NVKD_GetFooter", p);
            if (dt.Rows.Count > 0)
            {
                kltx = 0; klban = 0; ttban = 0; tongchenh = 0; chenhnvkd = 0; ttchenh = 0; ttchenhnvkd = 0; htrocuoc = 0; tiencabom = 0;

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    kltx += double.Parse(dt.Rows[0]["KLThucXuat"].ToString());
                    klban += double.Parse(dt.Rows[0]["KLKhachHang"].ToString());
                    ttban += double.Parse(dt.Rows[0]["TTGiaHD"].ToString());
                    tongchenh += double.Parse(dt.Rows[0]["ChenhKL"].ToString());
                    chenhnvkd += double.Parse(dt.Rows[0]["KLNVKD"].ToString());
                    ttchenh += double.Parse(dt.Rows[0]["TTChenh"].ToString());
                    ttchenhnvkd += double.Parse(dt.Rows[0]["TTNVKD"].ToString());
                    htrocuoc += double.Parse(dt.Rows[0]["TienHoTroCuoc"].ToString());
                    //tiencabom += double.Parse(dt.Rows[0]["TienCaBom"].ToString());
                }
                
                p = new SqlParameter[5];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p[1].SqlDbType = SqlDbType.SmallDateTime;
                p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                p[3] = new SqlParameter("@LoaiHD", int.Parse(dlHinhThuc.SelectedValue));
                p[3].SqlDbType = SqlDbType.Int;
                p[4] = new SqlParameter("@Index", index);
                p[4].SqlDbType = SqlDbType.Int;

                dt = ex.GetData("sp_BCDT_NVKD_GetTongTienBom", p);
                if (dt.Rows.Count > 0)
                {
                    tiencabom = double.Parse(dt.Rows[0]["TienCaBom"].ToString().Trim().Equals("") ? "0" : dt.Rows[0]["TienCaBom"].ToString());
                }
            }
        }
        if (GV.Rows.Count > 0)
        {
            GV.FooterRow.Cells[2].Text = "Tổng cộng";
            GV.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Center;
            GV.FooterRow.Cells[5].Text = ex.ConvertDecimal(kltx);
            GV.FooterRow.Cells[7].Text = ex.ConvertDecimal(klban);
            GV.FooterRow.Cells[10].Text = ex.ConvertDecimal(ttban);
            GV.FooterRow.Cells[11].Text = ex.ConvertDecimal(tongchenh);
            GV.FooterRow.Cells[12].Text = ex.ConvertDecimal(chenhnvkd);
            GV.FooterRow.Cells[13].Text = ex.ConvertDecimal(ttchenh);
            GV.FooterRow.Cells[14].Text = ex.ConvertDecimal(ttchenhnvkd);
            GV.FooterRow.Cells[15].Text = ex.ConvertDecimal(htrocuoc);
            GV.FooterRow.Cells[17].Text = ex.ConvertDecimal(tiencabom);
            GV.FooterRow.Cells[18].Text = ex.ConvertDecimal(tiencabom+htrocuoc+ttban);
        }
    }

    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();

        if (dt.Rows.Count > 0)
        {
            string sheetname = "BCDT-NVKD";

            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;
            

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            //hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;

            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            string ngaythang = dlThang.SelectedItem.Text.ToUpper()+" "+ dlNam.SelectedItem.Text.ToUpper();
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("BÁO CÁO DOANH THU NHÂN VIÊN KINH DOANH " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 12);
            sheet.AddMergedRegion(cra);

            rowIndex++;
            string ht = "";
            if (dlNhanVien.SelectedIndex > 0)
            {
                if (dlHinhThuc.SelectedIndex > 0)
                    ht = "-Hình thức: " + dlHinhThuc.SelectedItem.Text;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Nhân viên KD: " + dlNhanVien.SelectedItem.Text + "" + ht);
                r1c1.CellStyle = hs3;
                r1c1.Row.Height = 500;

                cra = new CellRangeAddress(3, 3, 0, 12);
                sheet.AddMergedRegion(cra);
            }
            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion
            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header = { "Ngày xuất","Khách hàng","VAT","Mác TX","KL TX","Mác HĐ","KL bán","Loại đá","Giá HĐ","TT bê tông","Tổng chênh","Chênh NVKD","TT chênh","TT chênh NVKD","Hỗ trợ cước","Loại ca bơm","Tiền ca bơm","Thành tiền hàng","NVKD","Hình thức HĐ" };

            var cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }
            #endregion
            //End header-----------------------------------------------------------------------
            //ghi du lieu tung dong
            int sodong = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);
                    if ((j==5 || j==7) || j >= 9 && j != 16 && j != 20 && j != 19)
                    {
                        if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                        }
                        cell.CellStyle = hsRight;
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j].ToString());

                        cell.SetCellValue(dt.Rows[i][j].ToString());
                        if (j <= 8 && j != 2)
                            cell.CellStyle = hsCenter;
                        else
                            if (j >= 9 && j != 16 && j != 20 && j != 19)
                                cell.CellStyle = hsRight;
                            else
                                cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;
                }
                if (i > 0)
                {
                    if (dt.Rows[i][1].ToString().Equals(dt.Rows[i - 1][1].ToString()) && dt.Rows[i][2].ToString().Equals(dt.Rows[i - 1][2].ToString()) && dt.Rows[i][16].ToString().Equals(dt.Rows[i - 1][16].ToString()) && dt.Rows[i][16].ToString().ToLower().Contains("ca"))
                    {
                        sodong++;
                        cra = new CellRangeAddress(rowIndex - sodong, rowIndex, 17, 17);
                        sheet.AddMergedRegion(cra);
                    }
                    else
                        sodong = 0;
                }

            }
            #region Footer
            //-----------Footer
            string[] footer = {"F","G","H","I","J","K","L","M", "N", "O", "P" };
            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(2);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            for (int k = 5; k <= 15; k++)
            {
                if (k != 6 && k != 8 && k != 9)
                {
                    cell = row.CreateCell(k);
                    cell.CellFormula = "Sum(" + footer[k - 5] + "7:" + footer[k - 5] + rowIndex.ToString() + ")";
                    cell.Row.Height = 500;
                    cell.CellStyle = hs4;
                }
            }
            cell = row.CreateCell(17);
            cell.SetCellValue(GV.FooterRow.Cells[17].Text.Trim());
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            cell = row.CreateCell(18);
            cell.SetCellValue(GV.FooterRow.Cells[18].Text.Trim());
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            //fix lai border
            for (int h = 0; h <= 20; h++)
            {
                if ((h <= 4 && h != 2) || h == 16 || h == 20 || h == 19|| h==6 || h==8 || h==9)
                {
                    r1c1 = row.CreateCell(h);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }

            }
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
            #endregion
            //end sheet tong hop--------------------------

            double cabom = 0;
            sodong = 0;
            string hinhthuc = "";
            if (dlNhanVien.SelectedIndex.Equals(0))
            {
                
                getDate();
                p = new SqlParameter[5];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p[1].SqlDbType = SqlDbType.SmallDateTime;
                p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                p[3] = new SqlParameter("@LoaiHD", int.Parse(dlHinhThuc.SelectedValue));
                p[3].SqlDbType = SqlDbType.Int;
                p[4] = new SqlParameter("@Index", 1);
                p[4].SqlDbType = SqlDbType.Int;

                dt = ex.GetData("sp_BCDT_NVKD_Print_NhanVien", p);

                int STT = 0;
                //ghi sheet con------------------------------------------
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    STT++;
                    #region Start
                    if (i > 0)
                    {
                        hinhthuc = dt.Rows[i]["NVKD"].ToString();

                        if (!hinhthuc.Equals(dt.Rows[i - 1]["NVKD"].ToString()))
                        {
                            cabom = 0;
                            STT = 0;
                            #region Footer
                            //-----------Footer
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(2);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            for (int k = 5; k <= 15; k++)
                            {
                                if (k != 6 && k != 8 && k != 9)
                                {
                                    cell = row.CreateCell(k);
                                    cell.CellFormula = "Sum(" + footer[k - 5] + "7:" + footer[k - 5] + rowIndex.ToString() + ")";
                                    cell.Row.Height = 500;
                                    cell.CellStyle = hs4;
                                }
                            }
                            //fix lai border
                            for (int h = 0; h <= 20; h++)
                            {
                                if ((h <= 4 && h != 2) || h == 16 || h == 20 || h==18|| h == 19 || h == 17 || h == 6 || h == 8 || h == 9)
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }

                            }
                            for (int ik = 0; ik < dt.Columns.Count; ik++)
                            {
                                sheet.AutoSizeColumn(ik);
                            }
                            //---End Footer
                            #endregion
                            sheet = workbook.CreateSheet(cl.EscapeSheetName(hinhthuc));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BÁO CÁO DOANH THU NHÂN VIÊN KINH DOANH " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            rowIndex++;

                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Nhân viên KD: " + hinhthuc);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion
                            #region Header1-2

                            //header2
                            rowIndex++;
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            
                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 0; j < header.Length; j++)
                            {
                                cell = row.CreateCell(j + 1);
                                cell.SetCellValue(header[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                            //End header-----------------------------------------------------------------------

                            STT++;

                        }
                    }
                    else
                    {
                        hinhthuc = "";
                        if (!hinhthuc.Equals(dt.Rows[i]["NVKD"].ToString()))
                        {
                            hinhthuc = dt.Rows[i]["NVKD"].ToString();
                            sheet = workbook.CreateSheet(cl.EscapeSheetName(hinhthuc));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("BÁO CÁO DOANH THU NHÂN VIÊN KINH DOANH " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Nhân viên KD: " + hinhthuc);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion
                            #region Header1-2

                            //header2
                            rowIndex++;
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 0; j < header.Length; j++)
                            {
                                cell = row.CreateCell(j + 1);
                                cell.SetCellValue(header[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                        }
                        for (int ik = 0; ik <= 20; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    cell = row.CreateCell(0);
                    cell.SetCellValue((STT).ToString());
                    cell.CellStyle = hsCenter;


                    for (int j = 1; j < dt.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);
                        if ((j == 5 || j == 7) || j >= 9 && j != 16 && j != 20 && j != 19)
                        {
                            if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                            }
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j].ToString());

                            cell.SetCellValue(dt.Rows[i][j].ToString());
                            if (j <= 8 && j != 2)
                                cell.CellStyle = hsCenter;
                            else
                                if (j >= 9 && j != 16 && j != 20 && j != 19)
                                    cell.CellStyle = hsRight;
                                else
                                    cell.CellStyle = headerLabelCellStyle;
                        }
                        cell.Row.Height = 500;
                    }
                    if (i > 0)
                    {
                        if (dt.Rows[i][1].ToString().Equals(dt.Rows[i - 1][1].ToString()) && dt.Rows[i][2].ToString().Equals(dt.Rows[i - 1][2].ToString()) && dt.Rows[i][16].ToString().Equals(dt.Rows[i - 1][16].ToString()) && dt.Rows[i][16].ToString().ToLower().Contains("ca"))
                        {
                            sodong++;
                            cra = new CellRangeAddress(rowIndex - sodong, rowIndex, 17, 17);
                            sheet.AddMergedRegion(cra);
                        }
                        else
                            sodong = 0;
                    }

                    #region Footer
                    if (i == dt.Rows.Count - 1)
                    {
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(2);
                        cell.SetCellValue("Tổng cộng");
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        for (int k = 5; k <= 15; k++)
                        {
                            if (k!=6 && k !=8 && k!=9)
                            {
                                cell = row.CreateCell(k);
                                cell.CellFormula = "Sum(" + footer[k - 5] + "7:" + footer[k - 5] + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;
                            }
                        }
                        //fix lai border
                        for (int h = 0; h <= 20; h++)
                        {
                            if ((h <= 4 && h != 2) || h == 16 || h == 20 || h == 19 || h == 18 || h == 17 || h == 6 || h == 8 || h == 9)
                            {
                                r1c1 = row.CreateCell(h);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }

                        }
                        for (int ik = 0; ik < dt.Columns.Count; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion
                }
                
            }
            else
                if (dlHinhThuc.SelectedIndex.Equals(0))
                {
                    getDate();
                    p = new SqlParameter[5];
                    p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                    p[0].SqlDbType = SqlDbType.SmallDateTime;
                    p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                    p[1].SqlDbType = SqlDbType.SmallDateTime;
                    p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
                    p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                    p[3] = new SqlParameter("@LoaiHD", int.Parse(dlHinhThuc.SelectedValue));
                    p[3].SqlDbType = SqlDbType.Int;
                    p[4] = new SqlParameter("@Index", 2);
                    p[4].SqlDbType = SqlDbType.Int;

                    dt = ex.GetData("sp_BCDT_NVKD_Print_HinhThuc", p);

                    int STT = 0;
                    //ghi sheet con------------------------------------------
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        STT++;
                        #region Start
                        if (i > 0)
                        {
                            hinhthuc = dt.Rows[i]["HinhThuc"].ToString();

                            if (!hinhthuc.Equals(dt.Rows[i - 1]["HinhThuc"].ToString()))
                            {
                                cabom = 0;
                                STT = 0;
                                #region Footer
                                //-----------Footer
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);

                                cell = row.CreateCell(2);
                                cell.SetCellValue("Tổng cộng");
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;

                                for (int k = 5; k <= 15; k++)
                                {
                                    if (k != 6 && k != 8 && k != 9)
                                    {
                                        cell = row.CreateCell(k);
                                        cell.CellFormula = "Sum(" + footer[k - 5] + "7:" + footer[k - 5] + rowIndex.ToString() + ")";
                                        cell.Row.Height = 500;
                                        cell.CellStyle = hs4;
                                    }
                                }
                                //fix lai border
                                for (int h = 0; h <= 20; h++)
                                {
                                    if ((h <= 4 && h != 2) || h == 16 || h == 20 || h == 18 || h == 19 || h == 17 || h == 6 || h == 8 || h == 9)
                                    {
                                        r1c1 = row.CreateCell(h);
                                        r1c1.CellStyle = hs4;
                                        r1c1.Row.Height = 400;
                                    }

                                }
                                for (int ik = 0; ik < dt.Columns.Count; ik++)
                                {
                                    sheet.AutoSizeColumn(ik);
                                }
                                //---End Footer
                                #endregion
                                sheet = workbook.CreateSheet(cl.EscapeSheetName(hinhthuc));
                                rowIndex = 0;

                                //Start header-----------------------------------------------------------------------
                                #region Header
                                //tao hnag dau tien

                                //cong ty
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                                r1c1.CellStyle = hs1;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(0, 0, 0, 4);
                                sheet.AddMergedRegion(cra);

                                //chi nhanh
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                                r1c1.CellStyle = hs1;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(1, 1, 0, 4);
                                sheet.AddMergedRegion(cra);

                                //tieu de bao cao
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("BÁO CÁO DOANH THU NHÂN VIÊN KINH DOANH " + ngaythang);
                                r1c1.CellStyle = hs2;
                                r1c1.Row.Height = 500;

                                cra = new CellRangeAddress(2, 2, 0, 12);
                                sheet.AddMergedRegion(cra);

                                rowIndex++;

                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Nhân viên KD: " + hinhthuc);
                                r1c1.CellStyle = hs3;
                                r1c1.Row.Height = 500;

                                cra = new CellRangeAddress(3, 3, 0, 12);
                                sheet.AddMergedRegion(cra);

                                //freeze panes
                                sheet.CreateFreezePane(0, 6);
                                #endregion
                                #region Header1-2

                                //header2
                                rowIndex++;
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);

                                cell = row.CreateCell(0);
                                cell.SetCellValue("STT");
                                cell.CellStyle = hs4;
                                cell.Row.Height = 400;


                                //tao tieu de cot
                                for (int j = 0; j < header.Length; j++)
                                {
                                    cell = row.CreateCell(j + 1);
                                    cell.SetCellValue(header[j].ToString());
                                    cell.CellStyle = hs4;
                                    cell.Row.Height = 500;
                                }
                                #endregion
                                //End header-----------------------------------------------------------------------

                                STT++;

                            }
                        }
                        else
                        {
                            hinhthuc = "";
                            if (!hinhthuc.Equals(dt.Rows[i]["HinhThuc"].ToString()))
                            {
                                hinhthuc = dt.Rows[i]["HinhThuc"].ToString();
                                sheet = workbook.CreateSheet(cl.EscapeSheetName(hinhthuc));
                                rowIndex = 0;

                                //Start header-----------------------------------------------------------------------
                                #region Header
                                //tao hnag dau tien

                                //cong ty
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                                r1c1.CellStyle = hs1;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(0, 0, 0, 4);
                                sheet.AddMergedRegion(cra);

                                //chi nhanh
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                                r1c1.CellStyle = hs1;
                                r1c1.Row.Height = 400;

                                cra = new CellRangeAddress(1, 1, 0, 4);
                                sheet.AddMergedRegion(cra);

                                //tieu de bao cao
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("BÁO CÁO DOANH THU NHÂN VIÊN KINH DOANH " + ngaythang);
                                r1c1.CellStyle = hs2;
                                r1c1.Row.Height = 500;

                                cra = new CellRangeAddress(2, 2, 0, 12);
                                sheet.AddMergedRegion(cra);

                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);
                                r1c1 = row.CreateCell(0);
                                r1c1.SetCellValue("Nhân viên KD: " + hinhthuc);
                                r1c1.CellStyle = hs3;
                                r1c1.Row.Height = 500;

                                cra = new CellRangeAddress(3, 3, 0, 12);
                                sheet.AddMergedRegion(cra);

                                //freeze panes
                                sheet.CreateFreezePane(0, 6);
                                #endregion
                                #region Header1-2

                                //header2
                                rowIndex++;
                                rowIndex++;
                                row = sheet.CreateRow(rowIndex);

                                cell = row.CreateCell(0);
                                cell.SetCellValue("STT");
                                cell.CellStyle = hs4;
                                cell.Row.Height = 400;


                                //tao tieu de cot
                                for (int j = 0; j < header.Length; j++)
                                {
                                    cell = row.CreateCell(j + 1);
                                    cell.SetCellValue(header[j].ToString());
                                    cell.CellStyle = hs4;
                                    cell.Row.Height = 500;
                                }
                                #endregion
                            }
                            for (int ik = 0; ik <= 19; ik++)
                            {
                                sheet.AutoSizeColumn(ik);
                            }
                        }
                        #endregion
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(0);
                        cell.SetCellValue((STT).ToString());
                        cell.CellStyle = hsCenter;


                        for (int j = 1; j < dt.Columns.Count; j++)
                        {
                            cell = row.CreateCell(j);
                            if ((j == 5 || j == 7) || j >= 9 && j != 16 && j != 20 && j != 19)
                            {
                                if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                                {
                                    cell.SetCellType(CellType.NUMERIC);
                                    cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                                }
                                cell.CellStyle = hsRight;
                            }
                            else
                            {
                                cell.SetCellValue(dt.Rows[i][j].ToString());

                                cell.SetCellValue(dt.Rows[i][j].ToString());
                                if (j <= 8 && j != 2)
                                    cell.CellStyle = hsCenter;
                                else
                                    if (j >= 9 && j != 16 && j != 20 && j != 19)
                                        cell.CellStyle = hsRight;
                                    else
                                        cell.CellStyle = headerLabelCellStyle;
                            }
                            cell.Row.Height = 500;
                        }
                        if (i > 0)
                        {
                            if (dt.Rows[i][1].ToString().Equals(dt.Rows[i - 1][1].ToString()) && dt.Rows[i][2].ToString().Equals(dt.Rows[i - 1][2].ToString()) && dt.Rows[i][16].ToString().Equals(dt.Rows[i - 1][16].ToString()) && dt.Rows[i][16].ToString().ToLower().Contains("ca"))
                            {
                                sodong++;
                                cra = new CellRangeAddress(rowIndex - sodong, rowIndex, 17, 17);
                                sheet.AddMergedRegion(cra);
                            }
                            else
                                sodong = 0;
                        }
                        #region Footer
                        if (i == dt.Rows.Count - 1)
                        {
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(2);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            for (int k = 5; k <= 15; k++)
                            {
                                if (k != 6 && k != 8 && k != 9)
                                {
                                    cell = row.CreateCell(k);
                                    cell.CellFormula = "Sum(" + footer[k - 5] + "7:" + footer[k - 5] + rowIndex.ToString() + ")";
                                    cell.Row.Height = 500;
                                    cell.CellStyle = hs4;
                                }
                            }
                            //fix lai border
                            for (int h = 0; h <= 20; h++)
                            {
                                if ((h <= 4 && h != 2) || h == 16 || h == 20 || h == 19 || h==18||h == 17 || h == 6 || h == 8 || h == 9)
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }

                            }
                            for (int ik = 0; ik < dt.Columns.Count; ik++)
                            {
                                sheet.AutoSizeColumn(ik);
                            }
                        }
                        #endregion
                    }
                }
            using (var exportData = new MemoryStream())
            {
                workbook.Write(exportData);
                string saveAsFileName="";
                if (dlNhanVien.SelectedIndex > 0)
                    saveAsFileName = "BCDT-NVKD-" + dlNhanVien.SelectedItem.Text + "-" + ngaythang + ".xls";
                else saveAsFileName = "BCDT-NVKD-" + ngaythang + ".xls";

                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                Response.Clear();
                Response.BinaryWrite(exportData.GetBuffer());
                Response.End();
            }
        }
    }
    protected void dlNhanVien_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlNhanVien.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
            p[1].SqlDbType = SqlDbType.SmallDateTime;
            p[2] = new SqlParameter("@NVKD", ex.GetGuid(dlNhanVien.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;

            dlHinhThuc.Items.Clear();
            dlHinhThuc.DataSource = ex.GetData("sp_BCDT_NVKD_LoadHinhThuc", p);
            dlHinhThuc.DataBind();
            dlHinhThuc.Items.Insert(0, new ListItem("--Chọn hình thức HĐ--", "0"));
            dlHinhThuc.SelectedIndex = 0;
        }
        else
        {
            dlHinhThuc.Items.Clear();
            dlHinhThuc.Items.Insert(0, new ListItem("--Chọn hình thức HĐ--", "0"));
            dlHinhThuc.SelectedIndex = 0;
        }
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            Search(CurrentPage);
        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }

    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;

        Search(CurrentPage);

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;

    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;

        Search(CurrentPage);

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;

    }
    protected void GV_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        for (int rowIndex = GV.Rows.Count - 2; rowIndex >= 0; rowIndex--)
        {
            GridViewRow gvRow = GV.Rows[rowIndex];
            GridViewRow gvPreviousRow = GV.Rows[rowIndex + 1];

            if (gvRow.Cells[16].Text.Trim().ToLower().Contains("ca") && gvRow.Cells[1].Text.Equals(gvPreviousRow.Cells[1].Text) && gvRow.Cells[2].Text.Equals(gvPreviousRow.Cells[2].Text) && gvRow.Cells[16].Text.Equals(gvPreviousRow.Cells[16].Text) && gvRow.Cells[17].Text.Equals(gvPreviousRow.Cells[17].Text))
            {

                if (gvPreviousRow.Cells[1].RowSpan < 2)
                {
                    gvRow.Cells[1].RowSpan = 2;
                    gvRow.Cells[2].RowSpan = 2;
                    gvRow.Cells[16].RowSpan = 2;
                    gvRow.Cells[17].RowSpan = 2;
                }
                else
                {
                    gvRow.Cells[1].RowSpan = gvPreviousRow.Cells[1].RowSpan + 1;
                    gvRow.Cells[2].RowSpan = gvPreviousRow.Cells[2].RowSpan + 1;
                    gvRow.Cells[16].RowSpan = gvPreviousRow.Cells[16].RowSpan + 1;
                    gvRow.Cells[17].RowSpan = gvPreviousRow.Cells[17].RowSpan + 1;
                }
                gvPreviousRow.Cells[1].Visible = false;
                gvPreviousRow.Cells[2].Visible = false;
                gvPreviousRow.Cells[16].Visible = false;
                gvPreviousRow.Cells[17].Visible = false;
            }
        }
    }

}